﻿using System;
using System.ComponentModel;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using SenseTimeTracker.Properties;
using StedySoft.SenseSDK;

namespace SenseTimeTracker.Misc
{
	public class Binder
	{
		private static string notCompletedTasks =
			"(SELECT projectId FROM Tasks WHERE " +
			"taskCompleted = 'false' OR " +
			"(taskCompleted = 'true' AND taskLockCompleted = 'false'))";

		/// <summary>
		/// Binds a combo box to ist vlaues
		/// </summary>
		/// <param name="clientFilterValue">An id for filtering clients</param>
		/// <param name="projectFilterValue">An id for filtering projects</param>
		/// <param name="indexValue">The index to be presented</param>
		/// <param name="allowAll">Allow selecting all</param>
		public static void BindCombobox(BindingList<SensePanelComboItem.Item> dataSource, Guid clientFilterValue, Guid projectFilterValue, bool allowAll, bool considerCompletedTasks)
		{
			if ((clientFilterValue != null) && (projectFilterValue != null))
			{
				SqlCeCommand command;
				Guid allMembersGuid;
				Guid clientFilter = (Guid)clientFilterValue;
				Guid projectFilter = (Guid)projectFilterValue;
				if (projectFilter != Guid.Empty)
				{
					command = new SqlCeCommand(
						"SELECT taskId AS id, CASE taskTerpExportLocation " +
						"WHEN 'false' THEN taskName ELSE taskName + ' ' + taskLocation END AS name FROM Tasks ",
						DatabaseManager.DbConnection);
					if ((clientFilter == DatabaseManager.AllClientsId) && (projectFilter == DatabaseManager.AllProjectsId))
					{
						command.CommandText += "WHERE 1=1 ";
					}
					else if (clientFilter == DatabaseManager.AllClientsId)
					{
						command.CommandText += "WHERE projectId = @projectId ";
						command.Parameters.Add("@projectId", projectFilter);
					}
					else if (projectFilter == DatabaseManager.AllProjectsId)
					{
						command.CommandText += "JOIN Projects ON Tasks.projectId = Projects.projectId WHERE Projects.clientId = @clientId ";
						command.Parameters.Add("@clientId", clientFilter);
					}
					else
					{
						command.CommandText += "JOIN Projects ON Tasks.projectId = Projects.projectId WHERE Projects.clientId = @clientId AND Projects.projectId = @projectId ";
						command.Parameters.Add("@clientId", clientFilter);
						command.Parameters.Add("@projectId", projectFilter);
					}

					if (!allowAll && considerCompletedTasks)
					{
						command.CommandText += "AND (taskCompleted = @taskCompleted OR taskLockCompleted = @taskLockCompleted)";
						command.Parameters.Add("@taskCompleted", false);
						command.Parameters.Add("@taskLockCompleted", false);
					}

					allMembersGuid = DatabaseManager.AllTasksId;
				}
				else if (clientFilter != Guid.Empty)
				{
					command = new SqlCeCommand(
						"SELECT projectId AS id, CASE projectNumber " +
						"WHEN '' THEN projectName ELSE projectName + ' (' + projectNumber + ')' END AS name " +
						"FROM Projects WHERE 1=1 ",
						DatabaseManager.DbConnection);
					if (clientFilter != DatabaseManager.AllClientsId)
					{
						command.CommandText += "AND clientId = @clientId";
						command.Parameters.Add("@clientId", clientFilterValue);
					}

					// select only projects having tasks
					if (!allowAll)
					{
						command.CommandText += " AND projectId IN " + notCompletedTasks;
					}

					allMembersGuid = DatabaseManager.AllProjectsId;
				}
				else
				{
					command = new SqlCeCommand("SELECT clientId AS id, clientName as name from Clients", DatabaseManager.DbConnection);

					// select only clients having projects having tasks
					if (!allowAll)
					{
						command.CommandText += " WHERE clientId IN (SELECT clientId FROM Projects WHERE projectId IN " + notCompletedTasks + ")";
					}

					allMembersGuid = DatabaseManager.AllClientsId;
				}

				if (allowAll)
				{
					dataSource.Add(new SensePanelComboItem.Item(Resources.AllSpecifier, allMembersGuid));
				}

				DatabaseManager.DbConnection.Open();
				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					dataSource.Add(new SensePanelComboItem.Item(reader["name"].ToString(), new Guid(reader["id"].ToString())));
				}

				DatabaseManager.DbConnection.Close();
			}
		}
	}
}
